﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Text;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using EZCRS.HCMS.Bll;
using System.Data.Common;
using System.Data.SqlClient;
using System.IO;

namespace EZCRS.HCMS.install
{
    public partial class index : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void btncreate_Click(object sender, EventArgs e)
        {
            string serverhost=serverip.Text.Trim();
            string serverdb=dbname.Text.Trim();
            string uname=username.Text.Trim();
            string upass=userpassword.Text.Trim();

            string connstr = "server="+serverhost+";User ID="+uname+";Password="+upass+";database="+serverdb+";Connection Reset=FALSE";

            if (!createdatabase(connstr))
            {
                Response.Write(CFun.AlertMeg("数据库联接错误，请检查数据库配置。。", "back", ""));
                Response.End();
            }

            Configuration cfg = System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration("~");
            ConnectionStringsSection css = cfg.ConnectionStrings;
            css.ConnectionStrings["connectionString"].ConnectionString = connstr;
            css.ConnectionStrings["mainDB"].ConnectionString = connstr;
            cfg.Save();

            Response.Write(CFun.AlertMeg("数据库建立成功！", "non", ""));
        }

        private bool createdatabase(string connstr)
        {
            SqlConnection conn = new SqlConnection(connstr);
            try
            {
                conn.Open();
            }
            catch
            {
                return false;
            }
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;

            //表
            ArrayList Lists = ExecuteSqlFile(Server.MapPath("hubs1_table.sql"));
            foreach (string varcommandText in Lists)
            {
                cmd.CommandText = varcommandText;            //为SqlCommand赋Sql语句;
                try { cmd.ExecuteNonQuery(); }
                catch { }                //执行
            }

            //视图
            Lists = ExecuteSqlFile(Server.MapPath("hubs1_view.sql"));
            foreach (string varcommandText in Lists)
            {
                cmd.CommandText = varcommandText;            //为SqlCommand赋Sql语句;
                try
                {
                    cmd.ExecuteNonQuery();
                }
                catch { }//执行
            }

            //存储过程
            Lists = ExecuteSqlFile(Server.MapPath("hubs1_procedure.sql"));
            foreach (string varcommandText in Lists)
            {
                cmd.CommandText = varcommandText;            //为SqlCommand赋Sql语句;
                try { cmd.ExecuteNonQuery(); }
                catch { }            //执行
            }

            //初始化数据
            Lists = ExecuteSqlFile(Server.MapPath("hubs1_initdata.sql"));
            foreach (string varcommandText in Lists)
            {
                cmd.CommandText = varcommandText;            //为SqlCommand赋Sql语句;
                try { cmd.ExecuteNonQuery(); }
                catch { }            //执行
            }

            conn.Close();
            return true;

        }

        #region ExecuteCommandWithSplitter方法
        /// <summary>
        /// 运行含有GO命令的多条SQL命令
        /// </summary>
        /// <param name="commandText">SQL命令字符串</param>
        /// <param name="splitter">分割字符串</param>
        public static void ExecuteCommandWithSplitter(string commandText, string splitter,string connstr)
        {
            int startPos = 0;

            do
            {
                int lastPos = commandText.IndexOf(splitter, startPos);
                int len = (lastPos > startPos ? lastPos : commandText.Length) - startPos;
                string query = commandText.Substring(startPos, len);

                if (query.Trim().Length > 0)
                {
                   
                        SqlCommand command = new SqlCommand(commandText, new SqlConnection(connstr));
                        command.Connection.Open();
                        command.ExecuteNonQuery();
                  
                }

                if (lastPos == -1)
                    break;
                else
                    startPos = lastPos + splitter.Length;
            } while (startPos < commandText.Length);

        }

        public static ArrayList ExecuteSqlFile(string varFileName)
        {
            //
            // TODO:读取.sql脚本文件
            //
            StreamReader sr = File.OpenText(varFileName);//传入的是文件路径及完整的文件名
            ArrayList alSql = new ArrayList();           //每读取一条语名存入ArrayList
            string commandText = "";
            string varLine = "";
            while (sr.Peek() > -1)
            {
                varLine = sr.ReadLine();
                if (varLine == "")
                {
                    continue;
                }
                if (varLine != "GO")
                {
                    commandText += varLine;
                    commandText += " ";
                }
                else
                {
                    alSql.Add(commandText);
                    commandText = "";
                }
            }

            sr.Close();
            return alSql;
        }
    

        #endregion ExecuteCommandWithSplitter方法结束

       
    }
}
